package com.yxj.util;

import com.kms.jca.UseKey;
import com.kms.provider.keyPair.ZyxxPrivateKey;
import com.kms.provider.keyPair.ZyxxPublicKey;
import com.sun.org.apache.xerces.internal.impl.dv.util.HexBin;
import com.yxj.ui.SwingArea;

import javax.swing.filechooser.FileSystemView;
import java.io.File;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

public class SqlLiteUtil {
    static Connection c;
    static Statement stmt;
    static UseKey useKey =new UseKey();

    public static String getPubKeyHex() {
        return pubKeyHex;
    }

    public static void setPubKeyHex(String pubKeyHex) {
        SqlLiteUtil.pubKeyHex = pubKeyHex;
    }

    static String pubKeyHex = "A68AC8A0B4289CE5FC5EFFF6C3A33296928A16A12FDC813290DAF121F0515CC1CFC540111455416AE6920656B18CA15D15EE7B85531C2C969C5E3B1A912B82CB";

    public static String getPriKeyHex() {
        return priKeyHex;
    }

    public static void setPriKeyHex(String priKeyHex) {
        SqlLiteUtil.priKeyHex = priKeyHex;
    }

    static String priKeyHex = "";


    public static void sqlInit(String path){
        System.out.println("path:"+path);
        try {
            String driverMysql ="com.mysql.jdbc.Driver";//驱动程序名
            String driverSqlite ="org.sqlite.JDBC";
            String url ="jdbc:MySQL://47.104.29.17:3306/keycyptotool";
            String user ="root";//MySQL配置时的用户名
            String password ="root";//MySQL配置时的密码


            Class.forName(driverSqlite);
            //Sqlite
            c = DriverManager.getConnection("jdbc:sqlite:"+path);
//            c = DriverManager.getConnection("jdbc:sqlite:E:\\keyCyptoTool\\keyCyptoTool.db");
            //mysql
//            c = DriverManager.getConnection(url,user,password);//连接数据库
            //System.out.println("Opened database successfully");
            stmt = c.createStatement();
            // 判断表是否存在c
            ResultSet rsTables = null;
            try {
                rsTables = c.getMetaData().getTables(null, null, "localinfo", null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (rsTables.next()) {
                } else {
                    stmt.executeUpdate("create table localinfo (id int,name longtext,pwd longtext,lastUpadteTime longtext);");
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void sqlClose(){
        try {
            stmt.close();
            c.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //添加
    public static void insert(Integer id,String name,String pwd){
        try {
            c.setAutoCommit(false);
            StringBuffer sb = new StringBuffer();
            byte[] pwdMi = useKey.cipherEncryptKeyPair("SM2", new ZyxxPublicKey(HexBin.decode(pubKeyHex)), pwd.getBytes());
            pwd = HexBin.encode(pwdMi);
            sb.append("INSERT INTO localinfo (id,name,pwd,lastUpadteTime) "
                    + "VALUES (\""+id+"\",\""+name+"\", \""+pwd+"\", \""+new SimpleDateFormat("yyy-MM-dd hh:mm:ss").format(new Date())+"\" );\n");
            stmt.executeUpdate(sb.toString());
            c.commit();
            sqlClose();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //查询总数
    public static int selectPwdCount(){
        ResultSet rs = null;
        int rowCount=0;
        try {
            rs = stmt.executeQuery("SELECT count(*) as rowCount FROM localinfo;");
            while (rs.next()){
                rowCount = rs.getInt("rowcount");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            rs.close();
            sqlClose();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return rowCount;
    }

    //查询
    public static Object[][] selectList()  {
        int rowCount=selectPwdCount();
        sqlInit(SwingArea.getDbPth());
        Object list [][]=null;
        ResultSet rs = null;
        try {
            list= new Object[rowCount][];
            rs = stmt.executeQuery("SELECT * FROM localinfo;");
            int tempRowCount=0;
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String pwd = rs.getString("pwd");
                //System.out.println("原加密密码："+pwd);
                byte[] mingPwd = useKey.cipherDecrypeKeyPair("SM2", new ZyxxPrivateKey(HexBin.decode(priKeyHex)), HexBin.decode(pwd));
                pwd = new String(mingPwd);
                //System.out.println("解密后密码："+pwd);
                String lastUpadteTime = rs.getString("lastUpadteTime");
                Object ol[] = {id,name,pwd,lastUpadteTime};
                list[tempRowCount]=ol;
                tempRowCount++;
            }
            rs.close();
            sqlClose();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    //删除
    public static void deleteById(Integer id){
        try {
            c.setAutoCommit(false);
            String sql = "DELETE from localinfo where ID="+id+";";
            //System.out.println("实际执行的sql："+sql);
            stmt.executeUpdate(sql);
            c.commit();
            sqlClose();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
    //更新
    public static void updateById(Integer id,String value){
        try {
            c.setAutoCommit(false);
            byte[] pwdMi = useKey.cipherEncryptKeyPair("SM2", new ZyxxPublicKey(HexBin.decode(pubKeyHex)), value.getBytes());
            value = HexBin.encode(pwdMi);
            String sql = "UPDATE localinfo set pwd = \""+value+"\" , lastUpadteTime = \""+new SimpleDateFormat("yyy-MM-dd hh:mm:ss").format(new Date())+"\"  where ID="+id+";";
            //System.out.println("实际执行的sql："+sql);
            stmt.executeUpdate(sql);
            c.commit();
            sqlClose();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}
